﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using YawetagLib.Reflection;
using YawetagLib.Utilities;

namespace YawetagLib.Data
{
    public static class SqlCommandHelper
    {
        public static IList<object> ExecuteQuery(string connectionString, string query, int? commandTimeout, object parameters)
        {
            return Execute<object>(connectionString, query, CommandType.Text, commandTimeout, parameters);
        }

        public static IList<T> ExecuteQuery<T>(string connectionString, string query, int? commandTimeout, object parameters) where T : new()
        {
            return Execute<T>(connectionString, query, CommandType.Text, commandTimeout, parameters);
        }

        public static IList<object> ExecuteQuery(string connectionString, int? commandTimeout, string query)
        {
            return ExecuteQuery(connectionString, query, commandTimeout, null);
        }

        public static IList<T> ExecuteQuery<T>(string connectionString, int? commandTimeout, string query) where T : new()
        {
            return ExecuteQuery<T>(connectionString, query, commandTimeout, null);
        }

        public static IList<object> ExecuteQuery(string connectionString, string query)
        {
            return ExecuteQuery(connectionString, query, null, null);
        }

        public static IList<T> ExecuteQuery<T>(string connectionString, string query) where T : new()
        {
            return ExecuteQuery<T>(connectionString, query, null, null);
        }

        public static IList<object> ExecuteQuery(SqlConnection connection, string query, int? commandTimeout, object parameters)
        {
            return Execute<object>(connection, query, CommandType.Text, commandTimeout, parameters);
        }

        public static IList<T> ExecuteQuery<T>(SqlConnection connection, string query, int? commandTimeout, object parameters) where T : new()
        {
            return Execute<T>(connection, query, CommandType.Text, commandTimeout, parameters);
        }

        public static IList<object> ExecuteQuery(SqlConnection connection, int? commandTimeout, string query)
        {
            return ExecuteQuery(connection, query, commandTimeout, null);
        }

        public static IList<T> ExecuteQuery<T>(SqlConnection connection, int? commandTimeout, string query) where T : new()
        {
            return ExecuteQuery<T>(connection, query, commandTimeout, null);
        }

        public static IList<object> ExecuteQuery(SqlConnection connection, string query)
        {
            return ExecuteQuery(connection, query, null, null);
        }

        public static IList<T> ExecuteQuery<T>(SqlConnection connection, string query) where T : new()
        {
            return ExecuteQuery<T>(connection, query, null, null);
        }

        public static IList<object> ExecuteMethodCall(string connectionString, string name, int? commandTimeout, object parameters)
        {
            return Execute<object>(connectionString, name, CommandType.StoredProcedure, commandTimeout, parameters);
        }

        public static IList<T> ExecuteMethodCall<T>(string connectionString, string name, int? commandTimeout, object parameters) where T : new()
        {
            return Execute<T>(connectionString, name, CommandType.StoredProcedure, commandTimeout, parameters);
        }

        public static IList<object> ExecuteMethodCall(string connectionString, string name, int? commandTimeout)
        {
            return ExecuteMethodCall(connectionString, name, commandTimeout, null);
        }

        public static IList<T> ExecuteMethodCall<T>(string connectionString, string name, int? commandTimeout) where T : new()
        {
            return ExecuteMethodCall<T>(connectionString, name, commandTimeout, null);
        }

        public static IList<object> ExecuteMethodCall(string connectionString, string name)
        {
            return ExecuteMethodCall(connectionString, name, null, null);
        }

        public static IList<T> ExecuteMethodCall<T>(string connectionString, string name) where T : new()
        {
            return ExecuteMethodCall<T>(connectionString, name, null, null);
        }

        public static IList<object> ExecuteMethodCall(SqlConnection connection, string name, int? commandTimeout, object parameters)
        {
            return Execute<object>(connection, name, CommandType.StoredProcedure, commandTimeout, parameters);
        }

        public static IList<T> ExecuteMethodCall<T>(SqlConnection connection, string name, int? commandTimeout, object parameters) where T : new()
        {
            return Execute<T>(connection, name, CommandType.StoredProcedure, commandTimeout, parameters);
        }

        public static IList<object> ExecuteMethodCall(SqlConnection connection, string name, int? commandTimeout)
        {
            return ExecuteMethodCall(connection, name, commandTimeout, null);
        }

        public static IList<T> ExecuteMethodCall<T>(SqlConnection connection, string name, int? commandTimeout) where T : new()
        {
            return ExecuteMethodCall<T>(connection, name, commandTimeout, null);
        }

        public static IList<object> ExecuteMethodCall(SqlConnection connection, string name)
        {
            return ExecuteMethodCall(connection, name, null, null);
        }

        public static IList<T> ExecuteMethodCall<T>(SqlConnection connection, string name) where T : new()
        {
            return ExecuteMethodCall<T>(connection, name, null, null);
        }

        private static IList<T> Execute<T>(SqlConnection connection, string cmdText, CommandType cmdType, int? commandTimeout, object parameters) where T : new()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }

            IList<T> records = new List<T>();
            using (SqlCommand command = new SqlCommand(cmdText, connection) { CommandType = cmdType })
            {
                if (commandTimeout.HasValue)
                {
                    command.CommandTimeout = commandTimeout.Value;
                }

                PrepareCommandParameters(command, parameters);
                ExecuteCommandReader(command, records);
            }

            return records;
        }

        private static IList<T> Execute<T>(string connectionString, string cmdText, CommandType cmdType, int? commandTimeout, object parameters) where T : new()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return Execute<T>(connection, cmdText, cmdType, commandTimeout, parameters);
            }
        }

        private static void PrepareCommandParameters(SqlCommand command, object parameters)
        {
            if (parameters != null)
            {
                foreach (PropertyInfo propertyInfo in parameters.GetType().GetProperties())
                {
                    command.Parameters.Add(new SqlParameter(propertyInfo.Name, parameters.GetAt(propertyInfo.Name)));
                }
            }
        }

        private static void ExecuteCommandReader<T>(SqlCommand command, ICollection<T> records) where T : new()
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                IList<string> names = new List<string>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    names.Add(reader.GetName(i));
                }

                AnonymousBuilder anonymousBuilder = typeof(T) == typeof(object)
                    ? new AnonymousBuilder(names.ToArray())
                    : null;

                foreach (IDataRecord record in reader)
                {
                    IList<object> values = new List<object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        values.Add(record[i] != DBNull.Value ? record[i] : null);
                    }

                    T instance = anonymousBuilder != null
                        ? (T)anonymousBuilder.CreateInstance(values.ToArray())
                        : CreateTypedInstance<T>(values);

                    records.Add(instance);
                }
            }
        }

        private static T CreateTypedInstance<T>(IList<object> values) where T : new()
        {
            T instance = new T();
            Type type = typeof(T);

            instance.CopyValues(type, values);

            return instance;
        }
    }
}
